import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
from sqlalchemy import create_engine
from sqlalchemy.pool import NullPool
from SKO.AbstractDPJob import AbstractDPJob
from Predict_FESJob import Predict_FESJob
class Predict_6626Job(AbstractDPJob):
    def __init__(self,
                 p_mode=None,p_st_no=None, p_aim_st_s=None):
        super(Predict_6626Job, self).__init__()
        self.mode = p_mode
        self.st_no = p_st_no
        self.aim_st_s = p_aim_st_s

        pass
    def execute(self):
        return self.do_execute()
    def do_execute(self):
        super(Predict_6626Job, self).do_execute()
        # 预测二炼钢硫平衡接口传入参数
        # 出钢记号、计划钢种目标S
        mode = self.mode
        st_no = self.st_no
        aim_st_s = self.aim_st_s
        msg = ''



        DB_HOST_MPP_DB2 = '10.70.48.41'
        DB_PORT_MPP_DB2 = 50021
        DB_DBNAME_MPP_DB2 = 'BGBDPROD'
        DB_USER_MPP_DB2 = 'm1admin'
        DB_PASSWORD_MPP_DB2 = 'm1adminbdzg'

        def getConnectionDb2(host, port, dbname, user, password):
            # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
            engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                                   encoding="utf-8", poolclass=NullPool)
            return engine.connect()

        # db_conn_mpp = getConnectionDb2(DB_HOST_MPP_DB2,
        #                                DB_PORT_MPP_DB2,
        #                                DB_DBNAME_MPP_DB2,
        #                                DB_USER_MPP_DB2,
        #                                DB_PASSWORD_MPP_DB2)

        # 根据出钢记号、计划钢种目标S，进行分组
        if st_no == 'IH2554A2':
            group = 1
            sql_condition = " AND ST_NO = 'IH2554A2'"
        elif st_no[:2] == 'IH' and st_no != 'IH2554A2':
            group = 2
            sql_condition = " AND ST_NO != 'IH2554A2' AND left(ST_NO,2) = 'IH' "
        elif st_no[:2] == 'IW':
            group = 3
            sql_condition = " AND left(ST_NO,2) = 'IW' "
        elif aim_st_s <= 15 and st_no[:2] not in ['IH', 'IW']:
            group = 4
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S <= 15"
        elif aim_st_s <= 20 and aim_st_s > 15 and st_no[:2] not in ['IH', 'IW']:
            group = 5
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 15 AND AIM_ST_S <= 20"
        elif aim_st_s <= 30 and aim_st_s > 20 and st_no[:2] not in ['IH', 'IW']:
            group = 6
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 20 AND AIM_ST_S <= 30"
        elif aim_st_s <= 50 and aim_st_s > 30 and st_no[:2] not in ['IH', 'IW']:
            group = 7
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 30 AND AIM_ST_S <= 50"
        elif aim_st_s <= 100 and aim_st_s > 50 and st_no[:2] not in ['IH', 'IW']:
            group = 8
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 50 AND AIM_ST_S <= 100"
        elif aim_st_s < 150 and aim_st_s > 100 and st_no[:2] not in ['IH', 'IW']:
            group = 9
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 100 AND AIM_ST_S < 150"
        elif aim_st_s < 180 and aim_st_s >= 150 and st_no[:2] not in ['IH', 'IW']:
            group = 10
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S >= 150 AND AIM_ST_S < 180"
        elif aim_st_s <= 250 and aim_st_s >= 180 and st_no[:2] not in ['IH', 'IW']:
            group = 11
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S >= 180 AND AIM_ST_S <= 250"
        else:
            group = 12
            sql_condition = " AND left(ST_NO,2) not in ('IW','IH') AND AIM_ST_S > 250"
        #本地编写目前读取本地文件，后续使用sql_condition进行拼接SQL
        start = datetime.datetime.now()
        delta_day2 = 10
        delta_day1 = delta_day2 + 365
        p_day_2 = (start - datetime.timedelta(days=delta_day2)).strftime("%Y%m%d")
        p_day_1 = (start - datetime.timedelta(days=delta_day1)).strftime("%Y%m%d")
        sql = " select PROD_DATE,ST_NO,AIM_ST_S,DES_WHISK_DEPTH1,STTEMP_AFIRON,AFTEMP_AFIRON  " \
              " FROM BGTAMOMMSM.T_ODS_TMMSM0701  " \
              " WHERE TC_PROC_FlAG = 2 AND PROD_DATE>='%s' " \
              " AND PROD_DATE<'%s' " \
              " %s ORDER BY PROD_DATE " % (p_day_1, p_day_2, sql_condition)
        print(sql)
        # df = pd.read_sql_query(sql, con=db_conn_mpp)
        # if df.empty is True:
        #     msg = '该钢种无分组'

        xlsx_name = 'D:/repos/sicost/test_0607.xlsx'
        df = pd.read_excel(xlsx_name)
        df.columns = df.columns.str.upper()
        df['PROD_DATE'] = df['PROD_DATE'].astype(str)

        def clean_data(df, gamma):

            column_name_list = df.columns.tolist()
            column_name_list.remove('PROD_DATE')
            column_name_list.remove('ST_NO')
            column_name_list.remove('AIM_ST_S')
            column_name_num = len(column_name_list)
            clean_str_start = 'df_new = df['
            clean_str_end = ']'
            ldict1 = {}
            for i in range(0, column_name_num):
                # print(i)
                # print(column_name_list[i])
                column_name_tmp = column_name_list[i]
                exec('''clean_str3 = "(df['{}'] > 0)"'''.format(column_name_tmp), locals(), ldict1)
                clean_str3 = ldict1["clean_str3"]
                if i == 0:
                    clean_str_start = clean_str_start + clean_str3
                else:
                    clean_str_start = clean_str_start + ' & ' + clean_str3
            clean_str = clean_str_start + clean_str_end
            # print(clean_str)
            exec(clean_str, locals(), ldict1)
            df_new = ldict1["df_new"]
            df_new = df_new.reset_index(drop=True)
            return df_new
        gamma = 1.5
        df_clean1 = clean_data(df, gamma)
        df_clean1['TEMP'] = df_clean1['STTEMP_AFIRON'] - df_clean1['AFTEMP_AFIRON']
        df_clean2 = df_clean1[(df_clean1['TEMP'] > 0)]
        df_clean2 = df_clean2.reset_index(drop=True)
        df_clean2.drop(['STTEMP_AFIRON'], axis=1, inplace=True)
        df_clean2.drop(['AFTEMP_AFIRON'], axis=1, inplace=True)
        model = LinearRegression()
        X = df_clean2['DES_WHISK_DEPTH1'].values.reshape(-1, 1)
        y = df_clean2['TEMP'].values
        model.fit(X, y)
        wenjiang_coef = float(model.coef_[0])

        return msg, wenjiang_coef